package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;

import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 质量报表
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/qualityReportController")
public class QualityReportController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(QualityReportController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 质量报表 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "qualityReportList")
	public ModelAndView qualityReportList(HttpServletRequest request)
	{
          
		return new ModelAndView("yongxin/kingdeeReport/qualityReportList");

	}


	/**
	 * easyui 质量报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

        
	String sql = " select *,(isnull(A,0) + isnull(B,0) + isnull(C,0) + isnull(D,0)) AS sum_js  from ( ";
		   sql += " select FDate,levels,COUNT(levels) as count from (  ";
		   sql += " select v1.FDate,t1005.FName AS levels from RemoteServer.AIS2009.dbo.ICQCBill v1  ";
		   sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0  ";
		   sql += " where 1=1  ";
		   sql += " AND v1.FDate between '2014-09-10' and '2014-09-30'";
		   sql += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0))  "; 
		   sql += "  ) t group by FDate,levels ";
		   sql += " ) m ";
		   sql += " pivot(MAX(count) for levels in(A,B,C,D) )a ";	

		

		List<Map<String, Object>> allmaplist = null;
		List<Map<String, Object>> maplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());
			
			
		} catch (Exception ex)
		{

		} finally
		{

		}
		
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		TagUtil.datagrid(response, dataGrid);

	}
     
	
	
	/**
	 * easyui 质量报表查询
	 * 
	 * @param request
	 * @param response
	 */

	@RequestMapping(params = "query")
	public ModelAndView query(HttpServletRequest request, HttpServletResponse response)
	{

		    String type = request.getParameter("type");
		    String date1 = request.getParameter("date1");
		    String date2 = request.getParameter("date2");	
		    
		    String FBatchNo = request.getParameter("batchNo");	
		    
		    request.setAttribute("type", type);
		    request.setAttribute("date1", date1);
		    request.setAttribute("date2",date2 );
		    request.setAttribute("FBatchNo", FBatchNo);
	  
	 // 查询质量报表明细表（按级别）    
     String sql = " select *,(A + B + C + D + E) AS SUM_JS	from(  ";
			sql += " select FDate, ";
			sql += "  MAX(case levels when 'A' then count else 0 end) A, ";
			sql += " MAX(case levels when 'B' then count else 0 end) B, ";
			sql += " MAX(case levels when 'C' then count else 0 end) C, "; 
			sql += " MAX(case levels when 'D' then count else 0 end) D, ";
			sql += " MAX(case levels when '不合格' then count else 0 end) E "; 
			sql += " from ( ";
			sql += " select FDate,levels,COUNT(levels) as count from ( ";
			sql += " select v1.FDate,t1005.FName AS levels from RemoteServer.AIS2009.dbo.ICQCBill v1 "; 
			sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0 "; 
			sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
			sql += " where 1=1 "; 
			sql += " AND v1.FDate between ? and ? ";
			sql += "  AND t14.FName like ? ";
			if(StringUtil.isNotEmpty(FBatchNo)){
			sql += " AND v1.FBatchNo like '%" + FBatchNo + "%'";
				
			}
			sql += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";  
			sql += " ) t group by FDate,levels ";
			sql += " ) t2 group by FDate ";
			sql += " )t3 ";
		
			
			// 查询质量报表明细表（按检测项目）    
/*			String sql2 = " select FQCItemName, ";
				  sql2 += " MAX(case levels when 'C' then count2 else 0 end) C, ";
				  sql2 += " MAX(case levels when 'D' then count2 else 0 end) D ";
				  sql2 += " from ( ";
				  sql2 += " select FQCItemName,levels,SUM(count1) AS count2 from ( ";
				  sql2 += " select FQCItemName,levels,COUNT(FQCItemName) AS count1 from ( ";
				  sql2 += " select  E_t32.FName AS FQCItemName,t1005.FName AS levels  ";
				  sql2 += " from RemoteServer.AIS2009.dbo.ICQCBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICQCBillEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  ";
				  sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.ICQCScheme v3 ON   v1.FSCBillInterID = v3.FInterID  AND v3.FInterID<>0 "; 
				  sql2 += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0  ";
				  sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.QMCheckItem E_t32 ON   u1.FQCItemID = E_t32.FID  AND E_t32.FID<>0 "; 
				  sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
				  sql2 += " where 1=1  ";
				  sql2 += " AND v1.Fdate between  ? and ? "; 
				  sql2 += " AND t14.FName = ? ";
				  sql2 += " AND t1005.FName not in('A','B') ";
				  sql2 += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";
				  sql2 += " ) A group by levels,FQCItemName	 ";
				  sql2 += " ) B group by levels,FQCItemName	 ";
				  sql2 += " ) b group by FQCItemName ";*/
			
			
	String sql2 = " select FQCItemName,C,case SUM_C when 0 then 0 else Convert(numeric(8,2), (cast(round(1.0*C/SUM_C,3) as numeric(8,4))*100)) end as per_c,	 ";
			sql2 += " D,case SUM_D when 0 then 0 else Convert(numeric(8,2), (cast(round(1.0*D/SUM_D,3) as numeric(8,4))*100)) end as per_d,	 "; 
			sql2 += " E,case SUM_E when 0 then 0 else Convert(numeric(8,2), (cast(round(1.0*E/SUM_E,3) as numeric(8,4))*100)) end as per_e	 "; 
			sql2 += " from (	 ";
			sql2 += " select * from ( ";
			sql2 += " select FQCItemName, ";
			sql2 += " MAX(case levels when 'C' then count2 else 0 end) C, ";
			sql2 += " MAX(case levels when 'D' then count2 else 0 end) D, ";
			sql2 += " MAX(case levels when '不合格' then count2 else 0 end) E ";
			sql2 += "  from ( ";
			sql2 += " select FQCItemName,levels,SUM(count1) AS count2 from ( ";
			sql2 += " select FQCItemName,levels,COUNT(FQCItemName) AS count1 from ( ";
			sql2 += " select  E_t32.FName AS FQCItemName,t1005.FName AS levels  ";
			sql2 += " from RemoteServer.AIS2009.dbo.ICQCBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICQCBillEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  ";
			sql2 += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0 "; 
			sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.QMCheckItem E_t32 ON   u1.FQCItemID = E_t32.FID  AND E_t32.FID<>0 "; 
			sql2 += "  LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
			sql2 += "  where 1=1  ";
			sql2 += " AND v1.Fdate between  ? and ?  ";
			sql2 += " AND t14.FName like ? ";
			sql2 += " AND t1005.FName not in('A','B') ";
			sql2 += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";
			sql2 += " ) t1 group by FQCItemName,levels ";	
			sql2 += " ) t2 group by levels,FQCItemName ";	   
			sql2 += " ) t3 group by FQCItemName ";
			sql2 += " ) t4  ";
			sql2 += " , ";
			sql2 += " ( ";
			sql2 += " select SUM(C) SUM_C,SUM(D) SUM_D,SUM(E) SUM_E from ( ";
			sql2 += " select FQCItemName,C,D,E from ( ";
			sql2 += " select FQCItemName, ";
			sql2 += "  MAX(case levels when 'C' then count2 else 0 end) C, ";
			sql2 += "  MAX(case levels when 'D' then count2 else 0 end) D, ";
			sql2 += " MAX(case levels when '不合格' then count2 else 0 end) E ";
			sql2 += " from ( ";
			sql2 += "  select FQCItemName,levels,SUM(count1) AS count2 from ( ";
			sql2 += " select FQCItemName,levels,COUNT(FQCItemName) AS count1 from ( ";
			sql2 += " select  E_t32.FName AS FQCItemName,t1005.FName AS levels  ";
			sql2 += " from RemoteServer.AIS2009.dbo.ICQCBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICQCBillEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  ";
			sql2 += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0  ";
			sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.QMCheckItem E_t32 ON   u1.FQCItemID = E_t32.FID  AND E_t32.FID<>0  ";
			sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
			sql2 += "  where 1=1 "; 
			sql2 += " AND v1.Fdate between  ? and ? "; 
			sql2 += " AND t14.FName like ? ";
			sql2 += " AND t1005.FName not in('A','B') ";
			sql2 += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";
			sql2 += " ) t1 group by FQCItemName,levels	 ";
			sql2 += "  ) t2 group by levels,FQCItemName	 ";   
			sql2 += "  ) t3 group by FQCItemName ";
			sql2 += " ) t4 ";
			sql2 += "  )t5 ";
			sql2 += " ) t6 ";
			sql2 += " )t7 ";
				
				   
				   
				 List<Map<String, Object>> maplist = null;
				 List<Map<String, Object>> maplist2 = null;
		         
				 if(type.equals("LZ")){
					 type = "%冷轧钢带%";
				 }else if(type.equals("DG")){
					 type = "%镀铬钢带%";
				 }else{
					 type = "%不锈钢带%";
				 }
				 
				 maplist = systemService.findForJdbc(sql,date1,date2,type);
				 
					
				 // 根据级别汇总
				 int sum_a = 0,sum_b = 0,sum_c = 0,sum_d = 0,sum_e = 0,sum_js = 0;
				 
				 for (Map<String, Object> map : maplist)
				{
					 sum_a +=  (Integer) map.get("A");
					 sum_b +=  (Integer) map.get("B");
					 sum_c +=  (Integer) map.get("C");
					 sum_d +=  (Integer) map.get("D");
					 sum_e +=  (Integer) map.get("E");
					 sum_js +=  (Integer) map.get("SUM_JS");
				}
				 
				 
				 request.setAttribute("sum_a", sum_a);
				 request.setAttribute("sum_b", sum_b);
				 request.setAttribute("sum_c", sum_c);
				 request.setAttribute("sum_d", sum_d);
				 request.setAttribute("sum_e", sum_e);
				 request.setAttribute("sum_js", sum_js);
				 
		         request.setAttribute("maplist", maplist);
		        
		        
		        
		         maplist2 = systemService.findForJdbc(sql2,date1,date2,type,date1,date2,type);
	             int sum_c1 = 0,sum_d1 = 0,sum_e1 = 0;
				 
				 for (Map<String, Object> map : maplist2)
				{
					 sum_c1 +=  (Integer) map.get("C");
					 sum_d1 +=  (Integer) map.get("D");
					 sum_e1 +=  (Integer) map.get("E");
				}
				 request.setAttribute("sum_c1", sum_c1);
				 request.setAttribute("sum_d1", sum_d1);
				 request.setAttribute("sum_e1", sum_e1);
		        
		        request.setAttribute("maplist2", maplist2);	
				
				return new ModelAndView("yongxin/kingdeeReport/qualityReportList");
	 }
	
	
	
	

	/**
	 * 
	 * 導出   
	 * 
	 * @param request
	 * @param response
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request, HttpServletResponse response) {
		
	     
		    String type = request.getParameter("type");
		    String date1 = request.getParameter("date1");
		    String date2 = request.getParameter("date2");	
		    String FBatchNo = request.getParameter("batchNo");	
			    

		    
			 // 查询质量报表明细表（按级别）    
		     String sql = " select *,(A + B + C + D + E) AS SUM_JS	from(  ";
					sql += " select FDate, ";
					sql += "  MAX(case levels when 'A' then count else 0 end) A, ";
					sql += " MAX(case levels when 'B' then count else 0 end) B, ";
					sql += " MAX(case levels when 'C' then count else 0 end) C, "; 
					sql += " MAX(case levels when 'D' then count else 0 end) D, ";
					sql += " MAX(case levels when '不合格' then count else 0 end) E "; 
					sql += " from ( ";
					sql += " select Convert(varchar(50),FDate,111) FDate,levels,COUNT(levels) as count from ( ";
					sql += " select v1.FDate,t1005.FName AS levels from RemoteServer.AIS2009.dbo.ICQCBill v1 "; 
					sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0 "; 
					sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
					sql += " where 1=1 "; 
					sql += " AND v1.FDate between ? and ? ";
					sql += "  AND t14.FName like ? ";
					if(StringUtil.isNotEmpty(FBatchNo)){
					sql += " AND v1.FBatchNo like '%" + FBatchNo + "%'";
						
					}
					sql += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";  
					sql += " ) t group by FDate,levels ";
					sql += " ) t2 group by FDate ";
					sql += " )t3 ";
				
					
					// 查询质量报表明细表（按检测项目）    
		/*			String sql2 = " select FQCItemName, ";
						  sql2 += " MAX(case levels when 'C' then count2 else 0 end) C, ";
						  sql2 += " MAX(case levels when 'D' then count2 else 0 end) D ";
						  sql2 += " from ( ";
						  sql2 += " select FQCItemName,levels,SUM(count1) AS count2 from ( ";
						  sql2 += " select FQCItemName,levels,COUNT(FQCItemName) AS count1 from ( ";
						  sql2 += " select  E_t32.FName AS FQCItemName,t1005.FName AS levels  ";
						  sql2 += " from RemoteServer.AIS2009.dbo.ICQCBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICQCBillEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  ";
						  sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.ICQCScheme v3 ON   v1.FSCBillInterID = v3.FInterID  AND v3.FInterID<>0 "; 
						  sql2 += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0  ";
						  sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.QMCheckItem E_t32 ON   u1.FQCItemID = E_t32.FID  AND E_t32.FID<>0 "; 
						  sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
						  sql2 += " where 1=1  ";
						  sql2 += " AND v1.Fdate between  ? and ? "; 
						  sql2 += " AND t14.FName = ? ";
						  sql2 += " AND t1005.FName not in('A','B') ";
						  sql2 += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";
						  sql2 += " ) A group by levels,FQCItemName	 ";
						  sql2 += " ) B group by levels,FQCItemName	 ";
						  sql2 += " ) b group by FQCItemName ";*/
					
					
			String sql2 = " select FQCItemName,C,case SUM_C when 0 then 0 else Convert(numeric(8,2), (cast(round(1.0*C/SUM_C,3) as numeric(8,4))*100)) end as per_c,	 ";
					sql2 += " D,case SUM_D when 0 then 0 else Convert(numeric(8,2), (cast(round(1.0*D/SUM_D,3) as numeric(8,4))*100)) end as per_d,	 "; 
					sql2 += " E,case SUM_E when 0 then 0 else Convert(numeric(8,2), (cast(round(1.0*E/SUM_E,3) as numeric(8,4))*100)) end as per_e	 "; 
					sql2 += " from (	 ";
					sql2 += " select * from ( ";
					sql2 += " select FQCItemName, ";
					sql2 += " MAX(case levels when 'C' then count2 else 0 end) C, ";
					sql2 += " MAX(case levels when 'D' then count2 else 0 end) D, ";
					sql2 += " MAX(case levels when '不合格' then count2 else 0 end) E ";
					sql2 += "  from ( ";
					sql2 += " select FQCItemName,levels,SUM(count1) AS count2 from ( ";
					sql2 += " select FQCItemName,levels,COUNT(FQCItemName) AS count1 from ( ";
					sql2 += " select  E_t32.FName AS FQCItemName,t1005.FName AS levels  ";
					sql2 += " from RemoteServer.AIS2009.dbo.ICQCBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICQCBillEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  ";
					sql2 += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0 "; 
					sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.QMCheckItem E_t32 ON   u1.FQCItemID = E_t32.FID  AND E_t32.FID<>0 "; 
					sql2 += "  LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
					sql2 += "  where 1=1  ";
					sql2 += " AND v1.Fdate between  ? and ?  ";
					sql2 += " AND t14.FName like ? ";
					sql2 += " AND t1005.FName not in('A','B') ";
					sql2 += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";
					sql2 += " ) t1 group by FQCItemName,levels ";	
					sql2 += " ) t2 group by levels,FQCItemName ";	   
					sql2 += " ) t3 group by FQCItemName ";
					sql2 += " ) t4  ";
					sql2 += " , ";
					sql2 += " ( ";
					sql2 += " select SUM(C) SUM_C,SUM(D) SUM_D,SUM(E) SUM_E from ( ";
					sql2 += " select FQCItemName,C,D,E from ( ";
					sql2 += " select FQCItemName, ";
					sql2 += "  MAX(case levels when 'C' then count2 else 0 end) C, ";
					sql2 += "  MAX(case levels when 'D' then count2 else 0 end) D, ";
					sql2 += " MAX(case levels when '不合格' then count2 else 0 end) E ";
					sql2 += " from ( ";
					sql2 += "  select FQCItemName,levels,SUM(count1) AS count2 from ( ";
					sql2 += " select FQCItemName,levels,COUNT(FQCItemName) AS count1 from ( ";
					sql2 += " select  E_t32.FName AS FQCItemName,t1005.FName AS levels  ";
					sql2 += " from RemoteServer.AIS2009.dbo.ICQCBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICQCBillEntry u1 ON   v1.FInterID = u1.FInterID  AND u1.FInterID<>0  ";
					sql2 += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t14 ON   v1.FItemID = t14.FItemID  AND t14.FItemID<>0  ";
					sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.QMCheckItem E_t32 ON   u1.FQCItemID = E_t32.FID  AND E_t32.FID<>0  ";
					sql2 += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Item t1005 ON   v1.FHeadSelft1456 = t1005.FItemID  AND t1005.FItemID<>0 "; 
					sql2 += "  where 1=1 "; 
					sql2 += " AND v1.Fdate between  ? and ? "; 
					sql2 += " AND t14.FName like ? ";
					sql2 += " AND t1005.FName not in('A','B') ";
					sql2 += " AND (v1.FTranType=713 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0)) ";
					sql2 += " ) t1 group by FQCItemName,levels	 ";
					sql2 += "  ) t2 group by levels,FQCItemName	 ";   
					sql2 += "  ) t3 group by FQCItemName ";
					sql2 += " ) t4 ";
					sql2 += "  )t5 ";
					sql2 += " ) t6 ";
					sql2 += " )t7 ";
						
						   
						   
						 List<Map<String, Object>> maplist = null;
						 List<Map<String, Object>> maplist2 = null;
				         
						 if(type.equals("LZ")){
							 type = "%冷轧钢带%";
						 }else if(type.equals("DG")){
							 type = "%镀铬钢带%";
						 }else{
							 type = "%不锈钢带%";
						 }
						 
					//	 maplist = systemService.findForJdbc(sql,date1,date2,type);
						 
							
						 // 根据级别汇总
						 /*
						 int sum_a = 0,sum_b = 0,sum_c = 0,sum_d = 0,sum_e = 0,sum_js = 0;
						 
						 for (Map<String, Object> map : maplist)
						{
							 sum_a +=  (Integer) map.get("A");
							 sum_b +=  (Integer) map.get("B");
							 sum_c +=  (Integer) map.get("C");
							 sum_d +=  (Integer) map.get("D");
							 sum_e +=  (Integer) map.get("E");
							 sum_js +=  (Integer) map.get("SUM_JS");
						}
						 
						 
						 request.setAttribute("sum_a", sum_a);
						 request.setAttribute("sum_b", sum_b);
						 request.setAttribute("sum_c", sum_c);
						 request.setAttribute("sum_d", sum_d);
						 request.setAttribute("sum_e", sum_e);
						 request.setAttribute("sum_js", sum_js);
						 */
				      //   request.setAttribute("maplist", maplist);
				        
				        
				        
				    //     maplist2 = systemService.findForJdbc(sql2,date1,date2,type,date1,date2,type);
			         /*
				         int sum_c1 = 0,sum_d1 = 0,sum_e1 = 0;
						 
						 for (Map<String, Object> map : maplist2)
						{
							 sum_c1 +=  (Integer) map.get("C");
							 sum_d1 +=  (Integer) map.get("D");
							 sum_e1 +=  (Integer) map.get("E");
						}
						 request.setAttribute("sum_c1", sum_c1);
						 request.setAttribute("sum_d1", sum_d1);
						 request.setAttribute("sum_e1", sum_e1);
				        
				        request.setAttribute("maplist2", maplist2);	  */
			    	
					//List<Map<String, Object>> maplist = null;

					try {
						
						maplist =systemService.findForJdbc(sql,date1,date2,type);
						maplist2 =systemService.findForJdbc(sql2,date1,date2,type,date1,date2,type);
						List<String[]> list = new ArrayList<String[]>();
                        
						int i = 0;
						
						for (Map<String, Object> map : maplist) {
							
							String[] arr = new String[maplist.get(0).size() +maplist2.get(0).size() ];
							arr[0] = (String) map.get("FDate");  
							arr[1] =    String.valueOf((Integer) map.get("sum_js"));
							arr[2] =     String.valueOf((Integer) map.get("A"));
							arr[3] =     String.valueOf((Integer) map.get("B"));
							arr[4] =    String.valueOf((Integer) map.get("C"));
							arr[5] =   String.valueOf((Integer) map.get("D"));
							arr[6] =    String.valueOf((Integer) map.get("E")); 
							
							if(i < maplist2.size()) {
							arr[7] = (String) maplist2.get(i).get("FQCItemName"); 
							arr[8] =    String.valueOf((Integer) maplist2.get(i).get("C"));
							arr[9] =     String.valueOf((BigDecimal) maplist2.get(i).get("per_c"));
							arr[10] =     String.valueOf((Integer) maplist2.get(i).get("D"));
							arr[11] =    String.valueOf((BigDecimal) maplist2.get(i).get("per_d"));
							arr[12] =   String.valueOf((Integer) maplist2.get(i).get("E"));
							arr[13] =    String.valueOf((BigDecimal) maplist2.get(i).get("per_e")); 
							}else{
								
							}
							i ++;
							list.add(arr);

						}

						
						
	                   int sum_a = 0,sum_b = 0,sum_c = 0,sum_d = 0,sum_e = 0,sum_js = 0;
						 
						 for (Map<String, Object> map : maplist)
						{
							 sum_a +=  (Integer) map.get("A");
							 sum_b +=  (Integer) map.get("B");
							 sum_c +=  (Integer) map.get("C");
							 sum_d +=  (Integer) map.get("D");
							 sum_e +=  (Integer) map.get("E");
							 sum_js +=  (Integer) map.get("SUM_JS");
						}
						 
                        int sum_c1 = 0,sum_d1 = 0,sum_e1 = 0;
						 
						 for (Map<String, Object> map : maplist2)
						{
							 sum_c1 +=  (Integer) map.get("C");
							 sum_d1 +=  (Integer) map.get("D");
							 sum_e1 +=  (Integer) map.get("E");
						}
						 
						 
						String[] sums = new String[]{"合计",String.valueOf(sum_js),String.valueOf(sum_a),String.valueOf(sum_b),String.valueOf(sum_c),String.valueOf(sum_d),String.valueOf(sum_e),"",String.valueOf(sum_c1),"",String.valueOf(sum_d1),"",String.valueOf(sum_e1)};
						list.add(sums);
						String[] columnNames = new String[] { "日期", "检验总数（卷）", "A级", "B级", "C级", "二等品", "不合格","项目", "C级项次）", "A级", "B级", "C级", "二等品", "不合格"};
						String titleName = "成品质量报表.xls";

						OutputStream fOut = response.getOutputStream();
						response.setHeader("Content-Disposition", "attachment; filename="
								+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

						ExcelUtils.create(request, list, columnNames, titleName, fOut);

					} catch (Exception ex) {

					}	
					
					
                  
	}
}


